package hk.org.ha.cdars;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;
import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.BeansException;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

import hk.org.ha.cdars.datacontext.BisDataContextImpl;
import hk.org.ha.cdars.datacontext.BisDataContext.Criteria;
import hk.org.ha.cdars.datacontext.BisDataContext.G1RefComboListCheckResult;
import hk.org.ha.cdars.datacontext.BisDataContext.G1RefG2CheckResult;
import hk.org.ha.cdars.datacontext.BisDataContext.G2RefComboListCheckResult;
import hk.org.ha.cdars.datacontext.BisDataContext.G2RefG1CheckResult;
import hk.org.ha.cdars.datacontext.BisDataContext.IpStep2ComboMissingNodeTypeInG1;
import hk.org.ha.cdars.datacontext.BisDataContext.IpStep2ComboMissingNodeTypeInG2;
import hk.org.ha.cdars.datacontext.BisDataContext.NodeTypeCheckResult;
import hk.org.ha.cdars.datacontext.BisDataContext.TreeTypeCheckResult;
import hk.org.ha.cdars.job.CheckJob;
import hk.org.ha.cdars.job.G1RefComboListCheckJob;
import hk.org.ha.cdars.job.G1RefG2CheckJob;
import hk.org.ha.cdars.job.G2RefComboListCheckJob;
import hk.org.ha.cdars.job.G2RefG1CheckJob;
import hk.org.ha.cdars.util.PoiUtil;

import lava.rt.common.ReflectCommon;
import lava.rt.linq.CommandExecuteExecption;
import lava.rt.linq.sql.TableTemplate;

@SpringBootApplication
public class BisDataCheckMain {

	private static final int ROW_LIMIT = 65535;

	private static int JOB_NO = 200;

	

	private static String RESULT_OUTPUT_DIR = "E:\\cdars-local-job";

	private static Criteria CR = BisDataContextImpl.CRITERIA;

	private static ApplicationContext context;

	private static BisDataContextImpl dataContext;

	

	public static void main(String[] args) throws Exception {
		context = SpringApplication.run(BisDataCheckMain.class, args);
		dataContext = context.getBean(BisDataContextImpl.class);

		

		println("startG1RefG2Check...");
		startG1RefG2Check();

		println("startG2RefG1Check...");
		startG2RefG1Check();

		//println("startG1RefComboListCheck...");
		//startG1RefComboListCheck();

		//println("startG2RefComboListCheck...");
		//startG2RefComboListCheck();

		//println("startTypeCheck...");
		//startTypeCheck();
		
		println("exprotResultToExcel...");
		exprotResultToExcel();
	}
	
	
	private static <E> int  exprotToExcel(Class<E> cls,List<E> results) throws Exception {
		int ret=0;
		 HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
		    //0
		    
			HSSFSheet sheet = WORK_BOOK.createSheet(cls.getSimpleName());

			int rowIndex = 0;

			HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);
			Map<String,Field> fieldMap=ReflectCommon.getAllDeclaredFieldMap(cls);
			fieldMap.remove("serialVersionUID");
			
		    String[] HEAD=new String[fieldMap.size()];
		    
		    int h=0;
		    for(Entry<String,Field> en :fieldMap.entrySet()) {
		    	HEAD[h++]=en.getKey();
		    }
			
			PoiUtil.setRowValues(headRow, HEAD);

			
			for (int i = 0; i < results.size(); i++) {
				rowIndex++;
				E resulti = results.get(i);
				String[] vals = getValues(HEAD, resulti);
				HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
				PoiUtil.setRowValues(valueRow, vals);
			}	
		
			File outputFile=new File(getOutputFilePath(cls));
		    WORK_BOOK.write(outputFile);
		    WORK_BOOK.close();
			
		return ret;
	}

	
	private static <E> int  exprotResultToExcel() throws Exception {
		int ret=0;
		
		ret+=exprotToExcel(NodeTypeCheckResult.class, dataContext.NODE_TYPE_CHECK_RESULT.selectList(null, CR.g1NodeType.asc));
		
		ret+=exprotToExcel(TreeTypeCheckResult.class, dataContext.TREE_TYPE_CHECK_RESULT.selectList(null, CR.g1TreeType.asc));
		
		ret+=exprotToExcel(IpStep2ComboMissingNodeTypeInG1.class, dataContext.IP_STEP2_COMBO_MISSING_NODE_TYPE_IN_G1.selectList(null, CR.path.asc));
		
		ret+=exprotToExcel(IpStep2ComboMissingNodeTypeInG2.class, dataContext.IP_STEP2_COMBO_MISSING_NODE_TYPE_IN_G2.selectList(null, CR.path.asc));
		
		return ret;
	}
	
	@Deprecated
	private static int startTypeCheck() throws Exception {
		// TODO Auto-generated method stub
		int ret=0;
	    
		
	    HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
	    //0
	    List<NodeTypeCheckResult> results=dataContext.NODE_TYPE_CHECK_RESULT.selectList(null, CR.g1NodeType.asc);
		HSSFSheet sheet = WORK_BOOK.createSheet(NodeTypeCheckResult.class.getSimpleName());

		int rowIndex = 0;

		HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);
	    
	    String[] HEAD=new String[] {
	    		 
				"G1_NODE_TYPE" 
				,"G2_NODE_TYPE"
				,"COMBO_NODE_TYPE"
				,"G1_FLAG"
				,"G2_FLAG"
		};
		
		PoiUtil.setRowValues(headRow, HEAD);

		
		for (int i = 0; i < results.size(); i++) {
			rowIndex++;
			NodeTypeCheckResult resulti = results.get(i);
			String[] vals = getValues(HEAD, resulti);
			HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
			PoiUtil.setRowValues(valueRow, vals);
		}
		
		
		//1
		
		    
		    List<TreeTypeCheckResult> results0=dataContext.TREE_TYPE_CHECK_RESULT.selectList(null, CR.g1TreeType.asc);
			 sheet = WORK_BOOK.createSheet(TreeTypeCheckResult.class.getSimpleName());

			 rowIndex = 0;

			 headRow = PoiUtil.getRow(sheet, rowIndex);
		    
		     HEAD=new String[] {
		    		 
					"G1_TREE_TYPE" 
					,"G2_TREE_TYPE"
					
					,"FLAG"
					
			};
			
			PoiUtil.setRowValues(headRow, HEAD);

			
			for (int i = 0; i < results0.size(); i++) {
				rowIndex++;
				TreeTypeCheckResult resulti = results0.get(i);
				String[] vals = getValues(HEAD, resulti);
				HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
				PoiUtil.setRowValues(valueRow, vals);
			}
		
		
		File outputFile=new File(getOutputFilePath("TypeCheck"));
	    WORK_BOOK.write(outputFile);
	    WORK_BOOK.close();
		return ret;
	}

	

	private static int startG2RefG1Check() throws Exception {
		// TODO Auto-generated method stub

		TableTemplate<G2RefG1CheckResult> table = dataContext.G2_REF_G1_CHECK_RESULT;
		table.delete(CR.jobNo.eq(null), JOB_NO);
		int ret = 0;
		G2RefG1CheckJob job = context.getBean(G2RefG1CheckJob.class);
		job.setJobNo(JOB_NO);
		List<G2RefG1CheckResult> results = job.check();
		ret += table.insertBatch(results);

		
		
		List<G2RefG1CheckResult> noSameresults = results.stream().filter(re -> {
			return !Objects.equals(CheckJob.Flag.相同.code, re.getFlag());
		}).collect(Collectors.toList());
		
		Collections.sort(noSameresults,(o1,o2)->{
			return o1.getG2Path().compareTo(o2.getG2Path());
		});

		String[] HEAD=new String[] {
				"TREE_TYPE" 
				,"NODE_TYPE"
				,"FLAG"  
				,"G2_PATH" 
				,"REMARK" 
		};
		
		HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
		
		for (int dataIndex = 0, pageNum = 0; dataIndex < noSameresults.size(); pageNum++) {

			HSSFSheet sheet = WORK_BOOK.createSheet(G2RefG1CheckResult.class.getSimpleName() + "-" + pageNum);

			int rowIndex = 0;

			HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);

			
			PoiUtil.setRowValues(headRow, HEAD);

			
			
			for (int i = dataIndex; i < noSameresults.size(); i++) {
				rowIndex++;
				
				G2RefG1CheckResult resulti = noSameresults.get(i);
				String[] vals = getValues(HEAD, resulti);
				HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
				PoiUtil.setRowValues(valueRow, vals);
				dataIndex++;
				if(dataIndex%ROW_LIMIT==0)break;
			}
		}

		File outputFile=new File(getOutputFilePath(G2RefG1CheckResult.class));
	    WORK_BOOK.write(outputFile);
	    WORK_BOOK.close();
		return ret;
	}

	private static int startG1RefG2Check() throws Exception {

		// TODO Auto-generated method stub

		TableTemplate<G1RefG2CheckResult> table = dataContext.G1_REF_G2_CHECK_RESULT;
		table.delete(CR.jobNo.eq(null), JOB_NO);
		int ret = 0;
		G1RefG2CheckJob job = context.getBean(G1RefG2CheckJob.class);
		job.setJobNo(JOB_NO);
		List<G1RefG2CheckResult> results = job.check();
		ret += table.insertBatch(results);
		HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
		HSSFSheet sheet = WORK_BOOK.createSheet(G1RefG2CheckResult.class.getSimpleName());

		int rowIndex = 0;

		HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);

		String[] HEAD=new String[] {
				"TREE_TYPE" 
				,"NODE_TYPE"
				,"FLAG"  
				,"G1_PATH" 
				,"REMARK" 
		};
		
		PoiUtil.setRowValues(headRow, HEAD);

		List<G1RefG2CheckResult> noSameresults = results.stream().filter(re -> {
			return !Objects.equals(CheckJob.Flag.相同.code, re.getFlag());
		}).collect(Collectors.toList());
		
		Collections.sort(noSameresults,(o1,o2)->{
			return o1.getG1Path().compareTo(o2.getG1Path());
		});
		
		for (int i = 0; i < noSameresults.size(); i++) {
			rowIndex++;
			G1RefG2CheckResult resulti = noSameresults.get(i);
			String[] vals = getValues(HEAD, resulti);
			HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
			PoiUtil.setRowValues(valueRow, vals);
		}
		File outputFile=new File(getOutputFilePath(G1RefG2CheckResult.class));
	    WORK_BOOK.write(outputFile);
	    WORK_BOOK.close();
		return ret;
	}

	private static int startG1RefComboListCheck() throws Exception {
		// TODO Auto-generated method stub

		TableTemplate<G1RefComboListCheckResult> table = dataContext.G1_REF_COMBO_LIST_CHECK_RESULT;
		table.delete(CR.jobNo.eq(null), JOB_NO);
		int ret = 0;
		G1RefComboListCheckJob job = context.getBean(G1RefComboListCheckJob.class);
		job.setJobNo(JOB_NO);
		List<G1RefComboListCheckResult> results = job.check();
		ret += table.insertBatch(results);
		
		
		
		List<G1RefComboListCheckResult> noSameresults = results.stream().filter(re -> {
			return Objects.equals(CheckJob.Flag.差異.code, re.getFlag());
		}).collect(Collectors.toList());

		Collections.sort(noSameresults,(o1,o2)->{
			return o1.getG1Path().compareTo(o2.getG1Path());
		});
		
		String[] HEAD=new String[] {
				"DESCRIPTION" 
				,"G1_COLUMN_ID" 
				,"G1_NODE_TYPE" 
				,"G1_TREE_TYPE"  
				,"FLAG" 
				,"G1_PATH" 
				,"REMARK"
		};
		HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
		for (int dataIndex = 0, pageNum = 0; dataIndex < noSameresults.size(); pageNum++) {

			HSSFSheet sheet = WORK_BOOK.createSheet(G1RefComboListCheckResult.class.getSimpleName() + "-" + pageNum);

			int rowIndex = 0;

			HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);

			
			PoiUtil.setRowValues(headRow, HEAD);

			
			
			for (int i = dataIndex; i < noSameresults.size(); i++) {
				rowIndex++;
				
				G1RefComboListCheckResult resulti = noSameresults.get(i);
				String[] vals = getValues(HEAD, resulti);
				HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
				PoiUtil.setRowValues(valueRow, vals);
				dataIndex++;
				if(dataIndex%ROW_LIMIT==0)break;
			}
		}
		File outputFile=new File(getOutputFilePath(G1RefComboListCheckResult.class));
	    WORK_BOOK.write(outputFile);
	    WORK_BOOK.close();
		
		return ret;
	}

	private static int startG2RefComboListCheck() throws Exception {
		// TODO Auto-generated method stub

		TableTemplate<G2RefComboListCheckResult> table = dataContext.G2_REF_COMBO_LIST_CHECK_RESULT;
		table.delete(CR.jobNo.eq(null), JOB_NO);
		int ret = 0;
		G2RefComboListCheckJob job = context.getBean(G2RefComboListCheckJob.class);
		job.setJobNo(JOB_NO);
		List<G2RefComboListCheckResult> results = job.check();
		ret += table.insertBatch(results);
		
		
		
		List<G2RefComboListCheckResult> noSameresults = results.stream().filter(re -> {
			return Objects.equals(CheckJob.Flag.差異.code, re.getFlag());
		}).collect(Collectors.toList());

		Collections.sort(noSameresults,(o1,o2)->{
			return o1.getG2Path().compareTo(o2.getG2Path());
		});
		
		String[] HEAD=new String[] {
				"DESCRIPTION" 
				,"G2_COLUMN_ID" 
				,"G2_NODE_TYPE" 
				,"G2_TREE_TYPE"  
				,"FLAG" 
				,"G2_PATH" 
				,"REMARK"
		};
		HSSFWorkbook WORK_BOOK=new HSSFWorkbook();
		for (int dataIndex = 0, pageNum = 0; dataIndex < noSameresults.size(); pageNum++) {

			HSSFSheet sheet = WORK_BOOK.createSheet(G2RefComboListCheckResult.class.getSimpleName() + "-" + pageNum);

			int rowIndex = 0;

			HSSFRow headRow = PoiUtil.getRow(sheet, rowIndex);

			
			PoiUtil.setRowValues(headRow, HEAD);

			
			
			for (int i = dataIndex; i < noSameresults.size(); i++) {
				rowIndex++;
				
				G2RefComboListCheckResult resulti = noSameresults.get(i);
				String[] vals = getValues(HEAD, resulti);
				HSSFRow valueRow = PoiUtil.getRow(sheet, rowIndex);
				PoiUtil.setRowValues(valueRow, vals);
				dataIndex++;
				if(dataIndex%ROW_LIMIT==0)break;
			}
		}
		File outputFile=new File(getOutputFilePath(G2RefComboListCheckResult.class));
	    WORK_BOOK.write(outputFile);
	    WORK_BOOK.close();
		
		return ret;
	}

	private static String[] getValues(String[] headNames, Object obj) {
		// TODO Auto-generated method stub
		String[] ret = new String[headNames.length];
		Map<String,Field> fieldMap=ReflectCommon.getTheDeclaredFieldMap(obj.getClass());
		try {
			for (int i = 0; i < headNames.length; i++) {
				String headNamei = headNames[i];
				Field field=fieldMap.get(headNamei);
				field.setAccessible(true);
				ret[i] = PoiUtil.format(field.get(obj));
			}
		} catch (Exception ex) {
           ex.printStackTrace();
		}
		return ret;
	}

	private static String getOutputFilePath(String checkName) {
		// TODO Auto-generated method stub
		String path=RESULT_OUTPUT_DIR+"/"+checkName+"-"+JOB_NO+".xls";
		return path;
	}
	
	private static String getOutputFilePath(Class cls) {
		// TODO Auto-generated method stub
		String path=RESULT_OUTPUT_DIR+"/"+cls.getSimpleName()+"-"+JOB_NO+".xls";
		return path;
	}

	private static void println(String... vals) {
		// TODO Auto-generated method stub
		System.out.println(String.join(",", vals));
	}
}
